<?php
class Customers extends Model{
	
	function getAll($id=0,$type='',$group=''){
		$sql ="SELECT * FROM customers c ";
		//$sql.="LEFT JOIN user_group g ON g.id=u.group_id ";
		//$sql.="LEFT JOIN user_position p ON p.id=u.pos_id ";
        $sql.="WHERE c.rev2<>'' ";
        //if($this->session->userdata('user_group')!= $this->config->item('admin') ): 
        if(($this->session->userdata('user_pos')== $this->config->item('ta') ) || ($this->session->userdata('user_pos')== $this->config->item('se') )  ):
            if(($this->session->userdata('user_group')== $this->config->item('user_dna') )   ): 
                $sql.="AND c.rev2='".$this->session->userdata('user_id')."'";
            endif;
        endif;
        //endif;
		if($type<>""):
			$sql.=" AND c.type='".$type."'";
		endif;
		if($group<>""):
			$sql.=" AND c.group_id=".$group."";
		endif;
		$sql.=" ORDER BY c.point ASC ";
		$query=$this->db->query($sql);
		return $query->result_array();
	}
    
    function getUserJob(){
        $sql =" SELECT u.username ";
        $sql.=" FROM users u ";
        $sql.=" WHERE ";
        $sql.=" sales=1 AND enabled=1 ";
        $sql.=" AND u.group_id=".$this->config->item('user_dna');
		$sql.=" AND (u.pos_id=".$this->config->item('ta')." OR u.pos_id=".$this->config->item('se').")"; 
        $query=$this->db->query($sql);
		return $query->result_array();
    }
    
    function getProspect(){
		$sql ="SELECT * FROM prospects p ";
		$sql.=" WHERE p.rev2='' ";
        $sql.=" ORDER BY p.id ASC ";
		$query=$this->db->query($sql);
		return $query->result_array();
	}
    
    function getProspectUsers($id){
		$sql ="SELECT * FROM prospects p ";
		$sql.=" WHERE p.rev2<>'' ";
		if($id<>""):
		$sql.=" AND p.rev2='".$id."'";	
		endif;
		$sql.=" ORDER BY p.id ASC ";
		$query=$this->db->query($sql);
		return $query->result_array();
	}
    
    function getLastActivity($id){
        $sql =" SELECT * FROM activity_transaction at ";
        $sql.=" LEFT JOIN activity a ON a.id=at.activity_id ";
        $sql.=" WHERE at.customer_id=".$id."";
		$sql.=" ORDER BY at.activity_date DESC ";
		$query=$this->db->query($sql);
		return $query->result_array();
    }
    
    function getLastJob($id){
        $sql ="SELECT *,j.id,((p.invest + (p.invest * j.ppn / 100) - j.discount) ) as total FROM jobs j ";
		$sql.="LEFT JOIN products p ON p.id=j.product_id ";
		$sql.="LEFT JOIN customers c ON c.id=j.customer_id ";
        $sql.=" WHERE j.customer_id=".$id."";
        $query=$this->db->query($sql);
		return $query->result_array();
    }
    
    function getSearch(){
        if($this->input->post('search')):
		    $this->db->where_not_in('rev2','');
			
            if($this->input->post('pic_search')<> ""):
                $this->db->where('rev2',$this->input->post('pic_search'));
            endif;
            
            if($this->input->post('group_search')<> ""):
                $this->db->where('group_id',$this->input->post('group_search'));
            endif;
            
            if($this->input->post('sector_search')<> ""):
                $this->db->where('sector_id',$this->input->post('sector_search'));
            endif;
            
            if($this->input->post('source_search')<> ""):
                $this->db->where('source_id',$this->input->post('source_search'));
            endif;
            
            if($this->input->post('status_search')<>""):
                $this->db->where('status_id',$this->input->post('status_search'));
            endif;
            
            if($this->input->post('type_search')<>""):
                $this->db->where('type',$this->input->post('type_search'));
            endif;
            
        else:
            $this->db->where('customer_name','error');
        endif;
        $query = $this->db->get('customers');
		return $query->result_array();
	}
	
	function getSearchDNA(){
        if($this->input->post('search')):
		    $this->db->select('*,customers.id');
			$this->db->join('customer_group','customer_group.id=customers.group_id','LEFT');
			$this->db->join('customer_sector','customer_sector.id=customers.sector_id','LEFT');
			$this->db->join('customer_source','customer_source.id=customers.source_id','LEFT');
			$this->db->join('customer_status','customer_status.id=customers.status_id','LEFT');
		    $this->db->where('rev2','');
			
            if($this->input->post('keywords')<> ""):
                $this->db->like('customer_name',$this->input->post('keywords'));
            endif;
			
			if($this->input->post('pic_search')<> ""):
                $this->db->where('rev',$this->input->post('pic_search'));
            endif;
            
            if($this->input->post('group_search')<> ""):
                $this->db->where('group_id',$this->input->post('group_search'));
            endif;
            
            if($this->input->post('sector_search')<> ""):
                $this->db->where('sector_id',$this->input->post('sector_search'));
            endif;
            
            if($this->input->post('source_search')<> ""):
                $this->db->where('source_id',$this->input->post('source_search'));
            endif;
            
            if($this->input->post('status_search')<>""):
                $this->db->where('status_id',$this->input->post('status_search'));
            endif;
            
        else:
            $this->db->where('customer_name','error');
        endif;
        $query = $this->db->get('customers');
		return $query->result_array();
	}
    
    
    function getUsers($type){
		$sql =" SELECT *,u.id FROM users u ";
		$sql.=" LEFT JOIN user_group g ON g.id=u.group_id ";
		$sql.=" LEFT JOIN user_position p ON p.id=u.pos_id ";
        $sql.=" WHERE  ";
        //$sql.=" pos_id=".$this->config->item('ta');
        $sql.=" u.enabled=1 AND u.sales=1  ";
        $sql.=" AND u.group_id=".$type;
		$sql.=" AND (u.pos_id=".$this->config->item('ta')." OR u.pos_id=".$this->config->item('se').")"; 
		$query=$this->db->query($sql);
		return $query->result_array();
	}
    
    function getCek(){
        $sql ="SELECT * FROM customers u ";
		$sql.="WHERE customer_name = '".$this->input->post('name')."' LIMIT 1";
        $query=$this->db->query($sql);
		return $query->row_array();
    }
    
    function getData($id){
		$sql ="SELECT *,c.id  FROM customers c ";
		$sql.="LEFT JOIN customer_group cg ON cg.id =c.group_id ";
		$sql.="LEFT JOIN customer_sector cs ON cs.id=c.sector_id ";
        $sql.="LEFT JOIN customer_source csr ON csr.id=c.source_id ";
        $sql.="LEFT JOIN customer_status cst ON cst.id=c.status_id ";
        $sql.="WHERE c.id = '".$id."' LIMIT 1";
		$query=$this->db->query($sql);
		return $query->row_array();
	}
    
    function getDataProspect($id){
		$sql ="SELECT *,p.id  FROM prospects p ";
        $sql.="WHERE p.id = '".$id."' LIMIT 1";
		$query=$this->db->query($sql);
		return $query->row_array();
	}
    
    function getSave(){
		if(($this->session->userdata('user_group')== $this->config->item('admin_dna')) || ($this->session->userdata('user_group')== $this->config->item('co_admin_dna'))):
			$rev =  $this->input->post('rev');
		else:
			$rev =  $this->session->userdata('user_id');
		endif;	
		
        $value = array  (
                            'sector_id'     =>  trim($this->input->post('sector')),
                            'rev_group'     =>  1,
                            'group_id'      =>  trim($this->input->post('group')),
                            'source_id'     =>  trim($this->input->post('source')),
                            'status_id'     =>  trim($this->input->post('status')),
                            'type'          =>  trim($this->input->post('type')),
                            'customer_name' =>  $this->input->post('name'),
                            'pic'           =>  $this->input->post('pic'),
                            'pic_pos'       =>  $this->input->post('pic_pos'),
                            'pic2'          =>  $this->input->post('pic'),
                            'pic2_pos'      =>  $this->input->post('pic_pos'),
                            'fax'           =>  $this->input->post('fax'),
                            'address'       =>  $this->input->post('address'),
                            'tlp'           =>  $this->input->post('tlp'),
                            'email'         =>  $this->input->post('email'),
                            'city'          =>  $this->input->post('city'),
                            'zip_code'      =>  $this->input->post('zipcode'),
                            'desc'          =>  $this->input->post('desc'),
							'rev2'          =>  $rev,
                            'enabled'       =>   1,
                            'user_stamp'=>  $this->session->userdata('user_id'),
                            'date_stamp'=>  date('Y-m-d')
                        );
        $this->db->insert('customers',$value);                
    }
    
    function getUpdate(){
		if(($this->session->userdata('user_group')== $this->config->item('admin_dna')) || ($this->session->userdata('user_group')== $this->config->item('co_admin_dna'))):
			$rev =  $this->input->post('rev');
		else:
			$rev =  $this->session->userdata('user_id');
		endif;	
		
        $value = array  (
                            'sector_id'     =>  trim($this->input->post('sector')),
                            'rev_group'     =>  1,
                            'group_id'      =>  trim($this->input->post('group')),
                            'source_id'     =>  trim($this->input->post('source')),
                            'status_id'     =>  trim($this->input->post('status')),
                            'type'          =>  trim($this->input->post('type')),
                            'customer_name' =>  $this->input->post('name'),
                            'pic'           =>  $this->input->post('pic'),
                            'pic_pos'       =>  $this->input->post('pic_pos'),
                            'pic2'          =>  $this->input->post('pic'),
                            'pic2_pos'      =>  $this->input->post('pic_pos'),
                            'fax'           =>  $this->input->post('fax'),
                            'address'       =>  $this->input->post('address'),
                            'tlp'           =>  $this->input->post('tlp'),
                            'email'         =>  $this->input->post('email'),
                            'city'          =>  $this->input->post('city'),
                            'zip_code'      =>  $this->input->post('zipcode'),
                            'desc'          =>  $this->input->post('desc'),
							'rev2'          =>  $rev,
                            'enabled'       =>   1,
                            'user_stamp'=>  $this->session->userdata('user_id'),
                            'date_stamp'=>  date('Y-m-d')
                        );
        $this->db->where('id',$this->input->post('id'));                 
        $this->db->update('customers',$value);                
    }
    
    function getRemove($id){
        $this->db->where('id',$id);
        $this->db->delete('customers');
    }
	
	function getRemoveImport(){
		$value['rev2']='';
		$this->db->where_not_in('rev','');
		$this->db->where_not_in('rev2','');
        $this->db->update('customers',$value);
	}
    
    function getRemoveProspect(){
        $this->db->where('rev2','');
        $this->db->delete('prospects');
    }
	
    function getRemoveProspectSelected(){
        $ids=$this->input->post('ids');
        $total=count($ids);
        for($i=0;$i<$total;$i++):
          if($total>$i):
            $this->db->where('id',$ids[$i]);
            $this->db->delete('prospects');
          endif;  
        endfor;
    }
    
    
    
	function getRemoveProspectUser(){
		$value['rev2']='';
        $this->db->where_not_in('rev2','');
        $this->db->update('prospects',$value);
    }
    
    function getRemoveProspectUserSelected(){ 
        $ids=$this->input->post('ids');
        $total=count($ids);
        for($i=0;$i<$total;$i++):
          if($total>$i):
            $value['rev2']=''; 
            $this->db->where('id',$ids[$i]);
            $this->db->update('prospects',$value);
          endif;  
        endfor;
    }
	
	function getProvideProspect(){
        $sql ="SELECT *,p.id  FROM prospects p ";
		$sql.="WHERE p.rev2='' ORDER BY RAND()";
        $query=$this->db->query($sql);
		$rows=$query->result_array();
		foreach($rows as $cust):
			$sql =" SELECT *,u.id FROM users u ";
            $sql.=" WHERE pos_id=4  ";
            $sql.=" AND u.group_id= ".$this->config->item('user_dna');
            $sql.=" AND u.pos_id= ".$this->config->item('ta') ;
            $sql.=" AND u.enabled=1 " ;
            $sql.=" AND u.sales=1 " ;
            $sql.=" ORDER BY RAND()";
			$query=$this->db->query($sql);
			$user=$query->row_array();
			//foreach($rows as $user):
				$value['rev2'] = $user['username'];
				$this->db->where('id',$cust['id']);
				$this->db->update('prospects',$value);
			//endforeach;
		endforeach;
	}
	
	function getProvideProspectCustomer($id){
		$sql ="SELECT * FROM prospects u ";
		$sql.="WHERE id=".$id." LIMIT 1";
		$query=$this->db->query($sql);
		$row=$query->row_array();
		$this->db->orderby('id','DESC');
		$query=$this->db->get('customers');
		$ids = $query->row_array();
		if(COUNT($ids)>=1):
			$ids = $ids['id'] + 1;
		else:
			$ids = 0 + 1;
		endif;
		
		$value = array  (
							'id'			=>	trim($ids),
                            'sector_id'     =>  1,
                            'group_id'      =>  1,
                            'source_id'     =>  1,
                            'status_id'     =>  1,
                            'rev_group'     =>  1,
                            'customer_name' =>  $row['customer_name'],
                            'type'          =>  $row['type'],
                            'pic'           =>  $row['pic'],
                            'pic_pos'       =>  $row['pic_pos'],
                            'pic2'          =>  $row['pic2'],
                            'pic2_pos'      =>  $row['pic2_pos'],
                            'fax'           =>  $row['fax'],
                            'address'       =>  $row['address'],
                            'tlp'           =>  $row['tlp'],
                            'email'         =>  $row['email'],
                            'city'          =>  $row['city'],
                            'zip_code'      =>  $row['zip_code'],
                            'desc'          =>  $row['desc'],
							'rev'           =>  '',
                            'rev2'          =>  $row['rev2'],
                            'rev_group'     =>   1,
                            'enabled'       =>   1,
                            'user_stamp'	=>  $this->session->userdata('user_id'),
                            'date_stamp'	=>  date('Y-m-d')
                        );
        $this->db->insert('customers',$value);
		$this->db->where('id',$id);
		$this->db->delete('prospects');
	}
}